﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Data.OleDb;

namespace Nova_DAL
{
    public class ExcelToSQL
    {
        DbHelper dbHelper = new DbHelper();
        DataSet ds = new DataSet();
        DbCommand cmd = null;
        string strSQL = "";

        public ExcelToSQL()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //    
        }

        public string Upload(System.Web.UI.HtmlControls.HtmlInputFile file)
        {
            string excelPath = "";
            if (file.PostedFile.ContentLength.ToString() != "")
            {
                string[] strSplit = file.PostedFile.FileName.Split(new char[] { '.' });
                string strUp = strSplit[1].ToString();
                int ira = 0;
                int irb = 0;
                int irc = 0;
                Random ra = new Random();
                ira = ra.Next(10000);
                irb = ra.Next(10000);
                irc = ra.Next(10000);
                //获取文件名称
                string strName = System.DateTime.Now.ToString("yyyyMMddhhmmss").Replace("-", "").Replace(" ", "").Replace(":", "") + ira.ToString() + irb.ToString() + irc.ToString() + "." + strUp;

                string strUrl = "~/Upload/" + strName;
                file.PostedFile.SaveAs(System.Web.HttpContext.Current.Server.MapPath(strUrl));

                //excelPath = "E://Nova//Upload//" + strName;
                excelPath = HttpContext.Current.Request.PhysicalApplicationPath + "Upload\\" + strName;
            }
            return excelPath;
        }

        public int ImportSql(System.Web.UI.HtmlControls.HtmlInputFile file, string tableName, string strTitle) //导入的Excel的路径，数据库里的表名     
        {
            string excelPath = Upload(file);
            if (excelPath == "")
                return (int)ImportState.excelCountError;

            if (!TableExist(tableName)) //表名是否存在
                return (int)ImportState.tableNameError;

            DataTable dt = ExcelToDataTable(excelPath, tableName, strTitle);
            if (dt == null)
            {
                return (int)ImportState.excelFormatError;
            }

            ArrayList tableField = GetTableField(tableName);   //表格的列名称
            string columnName = "PID,"; //Excel里的列名，增加一个ID列,如果ID自动递增则不需要增加ID列，只需要columnName=“”就可以了。
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnName += dt.Columns[i].ColumnName + ",";
                string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名
                for (int j = 0; j < tableField.Count; j++)
                {
                    if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
                        break;   //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++                  //Excel里的字段必须在Sql中都有
                    if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == tableField.Count - 1)
                        return (int)ImportState.fieldMatchError;
                }
            }
            int m = columnName.LastIndexOf(',');
            columnName = columnName.Remove(m);  //移除最后一个逗号

            for (int h = 0; h < dt.Rows.Count; h++)
            {
                string value = "'" + System.Guid.NewGuid().ToString() + "'" + ",";//如果ID自动递增ID列不需要增加了，那么value的初始值只需要value=“”就可以了。
                for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值
                {
                    value += "'" + dt.Rows[h][k].ToString() + "'" + ",";
                }
                value = value.Remove(0, 1);
                int n = value.LastIndexOf(',');
                value = value.Remove(n);//移除最后一个逗号
                n = value.LastIndexOf("'");
                value = value.Remove(n);
                try
                {
                    strSQL = "INSERT INTO " + tableName + "(" + columnName + ") VALUES('" + value + "')";
                    cmd = dbHelper.GetSqlStringCommond(strSQL);
                    dbHelper.ExecuteNonQuery(cmd);

                    string strPN = "";
                    if (tableName == "TP_Contract")
                    {
                        strPN = dt.Rows[h][2].ToString().Trim();
                    }
                    else
                    {
                        strPN = dt.Rows[h][1].ToString().Trim();
                    }
                    if (strPN != "")
                    {
                        strSQL = "SELECT COUNT(Project_No) FROM TP_Project WHERE Project_No='" + strPN + "'";
                        cmd = dbHelper.GetSqlStringCommond(strSQL);
                        DataSet ddss = dbHelper.ExecuteDataSet(cmd);
                        if (ddss.Tables[0].Rows[0][0].ToString() == "0")
                        {
                            strSQL = "INSERT INTO TP_Project(Project_No) VALUES('" + dt.Rows[h][1].ToString() + "')";
                            if (tableName == "TP_Contract")
                            {
                                strSQL = "INSERT INTO TP_Project(Project_No,Project_Time) VALUES('" + strPN + "','" + dt.Rows[h][1].ToString() + "')";
                            }
                            cmd = dbHelper.GetSqlStringCommond(strSQL);
                            dbHelper.ExecuteNonQuery(cmd);
                        }
                    }
                }
                catch (Exception err)
                {
                    throw err;
                    return (int)ImportState.dataTypeError;
                }
            }
            return (int)ImportState.right;
        }

        public DataTable ExcelToDataTable(string excelPath, string tableName, string strTitle)  //把Excel里的数据转换为DataTable，并返回DataTable 
        {

            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM [" + strTitle + "$]";
            DataTable dt;
            try
            {
                Conn.Open();
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds);
                Conn.Close();


                ArrayList tableField = GetTableField(tableName);   //表格的列名称
                ArrayList tableType = GetTableFieldType(tableName);   //表格的列名称
                int iColumns = ds.Tables[0].Columns.Count; //tableField.Count - 2;
                for (int k = 0; k < iColumns; k++)
                {
                    ds.Tables[0].Columns[k].ColumnName = tableField[k + 1].ToString();//修改excel列名
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        string strtext = ds.Tables[0].Rows[i][j].ToString();
                        if (tableType[j + 1].ToString() == "108")
                        {
                            if (string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString().Trim()) || ds.Tables[0].Rows[i][j].ToString().Trim() == "")
                            {
                                ds.Tables[0].Rows[i][j] = "0.00";
                            }
                            else
                            {
                                ds.Tables[0].Rows[i][j] = Convert.ToDouble(ds.Tables[0].Rows[i][j]).ToString("0.00");
                            }
                        }
                    }

                }
                dt = ds.Tables[0];
            }
            catch (Exception err)
            {
                throw err;
            }
            return dt;
        }

        public DataSet ExcelToDataTable(string excelPath)  //把Excel里的数据转换为DataTable，并返回DataTable 
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();

                //得到工作表的名称
                DataTable dt = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string tablename = dt.Rows[0][2].ToString().Trim();

                if (tablename.Length > 0)
                {

                    String sql = "SELECT * FROM  [" + tablename + "]";//可是更改Sheet名称，比如sheet2，等等    

                    OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                    DataSet OleDsExcle = new DataSet();
                    OleDaExcel.Fill(OleDsExcle, "Sheet1");
                    OleConn.Close();
                    return OleDsExcle;
                }
                return null;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        public bool TableExist(string tableName) //查看数据库里是否有此表名   
        {
            int iFlag = 0;
            try
            {
                string sql = "SELECT COUNT(NAME) FROM SYSOBJECTS WHERE NAME='" + tableName + "'";
                cmd = dbHelper.GetSqlStringCommond(sql);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    iFlag = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                    if (iFlag == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }

            return false;
        }

        public ArrayList GetTableField(string tableName)  //得到数据库某一个表中的所有字段 
        {
            ArrayList al = new ArrayList();

            try
            {
                strSQL = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "') order by b.colorder";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                DataSet ds = dbHelper.ExecuteDataSet(cmd);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        al.Add(ds.Tables[0].Rows[i][0].ToString());
                    }
                }
            }
            catch (Exception ex)
            { }
            return al;//返回的是表中的字段     
        }

        public ArrayList GetTableFieldType(string tableName)  //得到数据库某一个表中的所有字段类型
        {
            ArrayList al = new ArrayList();

            try
            {
                strSQL = "SELECT b.xtype FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "') order by b.colorder";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                DataSet ds = dbHelper.ExecuteDataSet(cmd);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        al.Add(ds.Tables[0].Rows[i][0].ToString());
                    }
                }
            }
            catch (Exception ex)
            { }
            return al;//返回的是表中的字段     
        }

        public enum ImportState
        {
            right = 1, //成功
            tableNameError = 2,//表名不存在
            fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配
            dataTypeError = 4, //转换数据类型时发生错误 
            excelFormatError = 5,//Excel格式不能读取     
            excelCountError = 6,//Excel不存在 
            SQLCountError = 7,//sql 存在此条数据，不允许重复添加
            Error = 8,
        }

        public void Alert(string str)
        {
            HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
        }


        public int ImpartSql_AddStorage(System.Web.UI.HtmlControls.HtmlInputFile file, string tableName, string strOID)
        {
            int iFlag = 0;

            string excelPath = Upload(file);
            if (excelPath == "")
                return (int)ImportState.excelCountError;

            if (!TableExist(tableName)) //表名是否存在
                return (int)ImportState.tableNameError;

            DataTable dt = ExcelToDataTable(excelPath, tableName, "Sheet1");
            if (dt == null)
            {
                return (int)ImportState.excelFormatError;
            }
            string strTrim = "";

            strSQL = "SELECT COUNT(StockNo) FROM TBill_AddStorage WHERE StockNo='" + dt.Rows[0][2].ToString() + "'";
            cmd = dbHelper.GetSqlStringCommond(strSQL);
            DataSet ds_tbad = dbHelper.ExecuteDataSet(cmd);
            if (ds_tbad.Tables[0].Rows[0][0].ToString() == "0")
            {
                strSQL = "INSERT INTO TBill_AddStorage(StockNo,Small_BillType,Contents,Bill_Status,OID,S01,S02) values('" + dt.Rows[0][2].ToString() + "','11','" + strTrim + "','0','" + strOID + "','" + dt.Rows[0][0].ToString() + "','" + dt.Rows[0][1].ToString() + "')";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.ExecuteNonQuery(cmd);

            }
            //else
            //{
            //    return (int)ImportState.SQLCountError;//主订单项目号存在
            //}

            DataTable dt_Info = ExcelToDataTable(excelPath, tableName, "Sheet2");
            if (dt_Info == null)
            {
                return (int)ImportState.excelFormatError;
            }

            for (int i = 0; i < dt_Info.Rows.Count; i++)
            {
                //查看物料表中是否存在该物料
                strSQL = "SELECT COUNT(MAT_CODE) FROM TBase_Material WHERE MAT_CODE='" + dt_Info.Rows[i][2].ToString() + "' AND MAT_NAME='" + dt_Info.Rows[i][3].ToString() + "' AND S01='" + dt_Info.Rows[i][4].ToString() + "'";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                DataSet ds_info = dbHelper.ExecuteDataSet(cmd);
                if (ds_info.Tables[0].Rows[0][0].ToString() == "0")
                {
                    strSQL = "INSERT INTO TBase_Material(MAT_CODE,MAT_NAME,S01) values('" + dt_Info.Rows[i][2].ToString() + "','" + dt_Info.Rows[i][3].ToString() + "','" + dt_Info.Rows[i][4].ToString() + "')";
                    cmd = dbHelper.GetSqlStringCommond(strSQL);
                    dbHelper.ExecuteNonQuery(cmd);
                }

                string strSec_Mat_ID = getSec_Mat_ID(dt_Info.Rows[i][2].ToString(), strOID);
                int iCount = Bind(strSec_Mat_ID, dt.Rows[0][2].ToString(), dt_Info.Rows[i][2].ToString());
                if (iCount > 0)
                {
                    strSQL = @"UPDATE TAddStorage_List SET Num=Num+'" + Convert.ToDecimal(dt_Info.Rows[i][7].ToString()) + "' WHERE PID='" + iCount + "'";
                }
                else
                {
                    strSQL = @"Insert into TAddStorage_List(StockNo,Mat_Code,Num,Act_Num,Sec_Mat_ID,DynamicState01,DynamicState02,DynamicState03,DynamicState04,DynamicState05,TS01,
TS02,TS03,TS04,TS05,S01,S02,S03,S04,S05,S06,S07,S08,S09,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,
V01,V02,V03,V04,V05,V06,V07,V08,V09,V10,VN01,VN02,VN03,VN04,VN05,Add_Flag1,Add_Flag2,Add_Flag3,Add_Flag4) values(@StockNo,@Mat_Code,@Num,@Act_Num,@Sec_Mat_ID,@DynamicState01,@DynamicState02,@DynamicState03,@DynamicState04,@DynamicState05,@TS01,
@TS02,@TS03,@TS04,@TS05,@S01,@S02,@S03,@S04,@S05,@S06,@S07,@S08,@S09,@S10,@S11,@S12,@S13,@S14,@S15,@S16,@S17,@S18,@S19,@S20,
@V01,@V02,@V03,@V04,@V05,@V06,@V07,@V08,@V09,@V10,@VN01,@VN02,@VN03,@VN04,@VN05,@Add_Flag1,@Add_Flag2,@Add_Flag3,@Add_Flag4)";
                }

                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@StockNo", DbType.String, dt.Rows[0][2].ToString());
                dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, dt_Info.Rows[i][2].ToString());
                dbHelper.AddInParameter(cmd, "@Num", DbType.Decimal, Convert.ToDecimal(dt_Info.Rows[i][7].ToString()));
                dbHelper.AddInParameter(cmd, "@Act_Num", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@Sec_Mat_ID", DbType.String, strSec_Mat_ID);
                dbHelper.AddInParameter(cmd, "@DynamicState01", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@DynamicState02", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@DynamicState03", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@DynamicState04", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@DynamicState05", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@TS01", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@TS02", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@TS03", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@TS04", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@TS05", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S01", DbType.String, dt_Info.Rows[0][1].ToString());
                dbHelper.AddInParameter(cmd, "@S02", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S03", DbType.String, dt_Info.Rows[0][6].ToString());
                dbHelper.AddInParameter(cmd, "@S04", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S05", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S06", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S07", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S08", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S09", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S10", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S11", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S12", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S13", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S14", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S15", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S16", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S17", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S18", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S19", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@S20", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V01", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V02", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V03", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V04", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V05", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V06", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V07", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V08", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V09", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@V10", DbType.String, "");
                dbHelper.AddInParameter(cmd, "@VN01", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@VN02", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@VN03", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@VN04", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@VN05", DbType.Decimal, 0);
                dbHelper.AddInParameter(cmd, "@Add_Flag1", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@Add_Flag2", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@Add_Flag3", DbType.Int16, 0);
                dbHelper.AddInParameter(cmd, "@Add_Flag4", DbType.Int16, 0);
                iFlag = dbHelper.ExecuteNonQuery(cmd);
                if (iFlag > 0)
                {
                    return (int)ImportState.right;
                }
                else
                {
                    return (int)ImportState.Error;
                }
            }

            return iFlag;
        }
        /// <summary>
        /// 判断是否添加过明细
        /// </summary>
        /// <param name="strSec_Mat_ID"></param>
        /// <returns></returns>
        protected int Bind(string strSec_Mat_ID, string strStroageNo, string Mat_Code)
        {
            strSQL = @"SELECT PID FROM TAddStorage_List WHERE StockNo=@StockNo AND Mat_Code=@Mat_Code AND Sec_Mat_ID=@Sec_Mat_ID";
            cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@StockNo", DbType.String, strStroageNo);
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, Mat_Code);
            dbHelper.AddInParameter(cmd, "@Sec_Mat_ID", DbType.String, strSec_Mat_ID);
            DataSet ds_TAL = dbHelper.ExecuteDataSet(cmd);
            if (ds_TAL != null && ds_TAL.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds_TAL.Tables[0].Rows[0][0].ToString());
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 生成二级编码
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        protected string getSec_Mat_ID(string strMat_Code, string strUserName)
        {
            cmd = dbHelper.GetStoredProcCommond("SP_Get_Sec_Mat_ID");
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, strMat_Code);
            dbHelper.AddInParameter(cmd, "@D01", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D02", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D03", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D04", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D05", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D06", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D07", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D08", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D09", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@D10", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S01", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S02", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S03", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S04", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S05", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S06", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S07", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S08", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S09", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S10", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S11", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S12", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S13", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S14", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S15", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S16", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S17", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S18", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S19", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@S20", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@N01", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N02", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N03", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N04", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N05", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N06", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N07", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N08", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N09", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@N10", DbType.Decimal, 0);
            dbHelper.AddInParameter(cmd, "@DynamicState01", DbType.Int16, 0);
            dbHelper.AddInParameter(cmd, "@DynamicState02", DbType.Int16, 0);
            dbHelper.AddInParameter(cmd, "@DynamicState03", DbType.Int16, 0);
            dbHelper.AddInParameter(cmd, "@DynamicState04", DbType.Int16, 0);
            dbHelper.AddInParameter(cmd, "@DynamicState05", DbType.Int16, 0);
            dbHelper.AddInParameter(cmd, "@TS01", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@TS02", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@TS03", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@TS04", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@TS05", DbType.String, "");
            dbHelper.AddInParameter(cmd, "@ForceNew", DbType.Int16, 1);
            dbHelper.AddInParameter(cmd, "@UserName", DbType.String, strUserName);
            dbHelper.AddOutParameter(cmd, "@Sec_Mat_ID", DbType.Int32, 20);
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int16, 10);
            dbHelper.AddOutParameter(cmd, "@ResMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int iSecs = Convert.ToInt32(dbHelper.GetParameter(cmd, "@Sec_Mat_ID").Value.ToString());
            string strSecs = iSecs.ToString();
            return strSecs;
        }
    }


   
}
